package cn.edu.cug.controller;

import cn.edu.cug.bean.DBBean;
import cn.edu.cug.bean.ResultResponse;
import cn.edu.cug.utils.DBConnector;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xhhuang on 2017/11/9.
 */

@Controller
@RequestMapping("/db")
public class DBController {
  private static final Logger LOG = LoggerFactory.getLogger(DBController.class);


  /**
   * 连接至数据库
   * 输入为用户提交的form表单
   *
   * @return
   */
  @RequestMapping(value = "/connect", method = RequestMethod.POST)
  @ResponseBody
  public ResultResponse connectToDB(@ModelAttribute("form") DBBean dbInfo,
                                    @RequestParam("dbDriver") String dbDriver,
                                    @RequestParam("dbUrl") String dbUrl,
                                    @RequestParam("dbUserName") String dbUserName,
                                    @RequestParam("dbPasswd") String dbPasswd,
                                    @RequestParam("dataBase") String database) {
    ResultResponse resultResponse = new ResultResponse();

    String info = dbDriver + "\t" + dbUrl + "\t" + dbUserName + "\t" + dbPasswd + "\t" + database;
    LOG.info(info);

    dbInfo.setDbDriver(dbDriver);
    dbInfo.setDbUrl(dbUrl);
    dbInfo.setDbUserName(dbUserName);
    dbInfo.setDbPasswd(dbPasswd);
    dbInfo.setDataBase(database);

    resultResponse.setData(info);

    return resultResponse;
  }


  /**
   * 输入用户执行的查询语句
   * 返回用户查询的结果
   *
   * @param SQLSentence
   * @return
   */
  @RequestMapping(value = "executeSQL", method = RequestMethod.POST)
  @ResponseBody
  public ResultResponse getSQLResult(@RequestParam("SQLSentence") String SQLSentence) throws IOException, SQLException {
    ResultResponse resultResponse = new ResultResponse();

    DBConnector dbConnector = new DBConnector();

    ResultSet resultSet = dbConnector.getSQLResult(dbConnector.getConnection(), SQLSentence);

    List list = new ArrayList();

    while (resultSet.next()) {
      Map map=new HashMap();
      String id = resultSet.getString(1);
      String teamName = resultSet.getString(2);
      String edulevel=resultSet.getString(3);
      String typeid=resultSet.getString(4);
      map.put("id",id);
      map.put("teamName",teamName);
      map.put("edulevel",edulevel);
      map.put("typeid",typeid);
      list.add(map);
    }

    resultResponse.setData(list);
    LOG.info("ResultResponse Info: "+resultResponse.getCode()+""+resultResponse.getData());

    return resultResponse;
  }
}
